# -*- coding: utf-8 -*-

import matplotlib
matplotlib.use('QT4Agg')
from matplotlib.backends.backend_qt4agg import FigureCanvasQTAgg as FigureCanvas
from matplotlib.backends.backend_qt4agg import NavigationToolbar2QTAgg as NavigationToolbar

import numpy as np
import matplotlib.pyplot as plt

import sqlite3

'''
Class to create the graphics
@author: Edgar Pereira
Nº 6207
'''

class MyPlot():
    #-------------------------------------------------------------------------
    # Class constructor
    #-------------------------------------------------------------------------  
    def __init__(self, graph, ano):
        # to select the graphic
        if graph == 0:
            self.total_prof_ano()
        elif graph == 1:
            self.total_prof_inst(ano)
            pass
        elif graph == 2:
            self.total_prof_grau(ano)
            pass
        elif graph == 3:
            self.total_prof_grau_est(ano)
            pass
        pass 
    
    
    #-------------------------------------------------------------------------
    # Graphic with total professor per year statistic
    #-------------------------------------------------------------------------
    def total_prof_ano(self):
        
        # the necessary lists
        ntpa = [] # Total nº of teachers year
        years = [] # year
        ind = [] # index
        
        for ano in range(0, 10):
            conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # connect to teh database
            cursor = conn.cursor()
            try:
                cmd = """SELECT COUNT (*) FROM docentes""" # serch the data
                cursor.execute(cmd)
                r = cursor.fetchone() # fetch result
                ntpa.append(r[0]) # append total
                years.append('200{0}'.format(ano)) # append year
                ind.append(ano) # append index

                
            except:
                print ('ERROR')
            pass
            
        plt.bar(ind, ntpa, width=0.8, bottom=0, color='r') # creates a bar graph   
##        plt.plot(years, ntpa)
        plt.xticks(ind, ('2000', '2001', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010') )
        plt.xlabel('Ano - 2000 a 2009')
        plt.title('Total de Professores por Ano')
        plt.grid(True)
        plt.axis([0, 10, 30000, 35000]) # select the axis values to show (to make the reading easy
        plt.show()
    pass
    
    
    #-------------------------------------------------------------------------
    # Graphic with total professor per instituition in one year
    #-------------------------------------------------------------------------
    def total_prof_inst(self, ano):
        
        # the necessary lists
        ntpi = [] # total number of teachers per institution
        ind = [] # index
        inst = [] # institution name
        ind1 = [] # index one
        i = 1 # the i value, were the 1st bar starts
        j = 1.4 # the j value, were the 'labels' os each bar stand
        conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # make the connection
        cursor = conn.cursor()
        
        try: # search the data
            cmd = """SELECT     estabelecimento,
                     COUNT(DISTINCT docente) AS Total_docentes
                     FROM       fichas_docencia
                     GROUP BY	estabelecimento
                     ORDER by   Total_docentes DESC
                     LIMIT 10"""
            cursor.execute(cmd)
            r = cursor.fetchall() # fetch values
            for row in r:
                inst.append(row[0]) # the instituition names
                ntpi.append(row[1]) # the totals
                ind.append(i) # index
                ind1.append(j) # index one
                i += 1
                j += 1
                
        except:
            print ('ERROR')
        pass
        
        plt.bar(ind, ntpi, width=0.8, bottom=0, color='r') # bar graph
        ##plt.plot(ntpi)
        plt.xticks(ind1, (inst), rotation='vertical', verticalalignment='bottom', horizontalalignment='right', size = 10)
        plt.xlabel('Estabelecimentos')
        plt.title('Total de Professores por Instituicao no ano de 200{0} - (10+)'.format(ano))
        plt.grid(True)
        plt.show()
    pass
    
    
    
    #-------------------------------------------------------------------------
    # Graphic with total professor per degree in one year
    #-------------------------------------------------------------------------
    def total_prof_grau(self, ano):
        
        # the necessary lists
        ntpg = [] # total nº of teachers by degree list
        grau = [] # degree names list
        ind = [] # index list
        ind1 = [] # index one list
        i = 1
        j = 1.4
        conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # database connection
        cursor = conn.cursor()
        
        try: # search to databese
            cmd = """SELECT     grau,
                     COUNT(DISTINCT docente) AS Total_docentes
                     FROM       fichas_docencia
                     GROUP BY	grau
                     ORDER by   Total_docentes DESC
                     LIMIT 10"""
            cursor.execute(cmd)
            r = cursor.fetchall() # fetch data
            for row in r:
                grau.append(row[0]) # append degree names
                ntpg.append(row[1]) # append total of teachers
                ind.append(i) # append index
                ind1.append(j) # append index one
                i += 1
                j += 1
                
        except:
            print ('ERROR')
        pass
        
        plt.bar(ind, ntpg, width=0.8, bottom=0, color='r') # make bar graph
##        plt.plot(ntpg)
        plt.xticks(ind1, (grau), rotation='vertical', verticalalignment='bottom', horizontalalignment='right', size = 10)
        plt.xlabel('Graus')
        plt.title('Total de Professores por Grau no ano de 200{0} - (10+)'.format(ano))
        plt.grid(True)
        plt.show()
    pass
    
    
    
    #-------------------------------------------------------------------------
    # Graphic with total professor per degree and institution in one year
    #------------------------------------------------------------------------
    def total_prof_grau_est(self, ano):
        
        # the necessary lists
        nt = [] # total nº of teachers per degree and institutin list
        grau = [] # degree list
        est = [] # institution names list
        ind = [] # index list
        ind1 = [] #index one list
        i = 1
        j = 1.4
        conn = sqlite3.connect('rebides200{0}.db'.format(ano)) # database connection
        cursor = conn.cursor()
        
        try: # search to database
            cmd = """SELECT grau, estabelecimento, 
                     COUNT(DISTINCT docente) AS Total_docentes
                     FROM		fichas_docencia
                     GROUP BY	grau, estabelecimento
                     ORDER by Total_docentes DESC
                     LIMIT 20"""
            cursor.execute(cmd)
            r = cursor.fetchall() #fetch data
            for row in r:
                grau.append(row[0] + ', ' + row[1]) # append degree and instituin name do list
                est.append(row[1]) # append institution name to list
                nt.append(row[2]) # append total to list
                ind.append(i) # append index to list
                ind1.append(j) # append index one to list
                i += 1
                j += 1
                
        except:
            print ('ERROR')
        pass
        
        
        plt.bar(ind, nt, width=0.8, bottom=0, color='r') # make bar graph
##        plt.plot(ntpg) # make line graph
        plt.xticks(ind1, (grau), rotation='vertical', verticalalignment='bottom', horizontalalignment='right', size = 10 )
        plt.title('Total de Professores por Grau e Instituicao no ano de 200{0} - (20+)'.format(ano))
##        plt.show()
        plt.grid(True)
        plt.show()
    pass

  
##mp = MyPlot()
##mp.total_prof_ano()
##mp.total_prof_inst(0)
##mp.total_prof_grau(0)
##mp.total_prof_grau_est(0)